﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SharePointOptimizer.Model;
using System.Linq;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using System.Data.SqlTypes;
using AG.Utilities;
using SharePointOptimizer.Model;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using System.Data.SqlTypes;
namespace SharePointOptimizer.SQLServer
{

    public class CollectSharePointUsageData
    {
        List<Exception> errors;
        SharePointOptimizerDataContext dc;

        public List<Exception> Collect()
        {

            try
            {
                errors = new List<Exception>();
                dc = new SharePointOptimizerDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());
                // dc.ClearAll();

                //TODO: KK Put collection code here
                string connstring = SPUsageApplicationProxy.Default.DatabaseConnectionString;
                using (SqlConnection conn = new SqlConnection(connstring))
                {

                    conn.Open();

                    SqlCommand sqlcommand = new SqlCommand("proc_GetSlowestTimerJobs", conn);
                    sqlcommand.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter param = new SqlParameter("@StartTime", System.Data.SqlDbType.DateTime);
                    param.Value = DateTime.Today.AddMonths(-1);
                    sqlcommand.Parameters.Add(param);
                    SqlParameter param2 = new SqlParameter("@EndTime", System.Data.SqlDbType.DateTime);
                    param2.Value = DateTime.Today;
                    sqlcommand.Parameters.Add(param2);
                    SqlParameter param3 = new SqlParameter("@MachineName", System.Data.SqlDbType.NVarChar);
                    param3.Value = DBNull.Value;
                    sqlcommand.Parameters.Add(param3);
                    SqlParameter param4 = new SqlParameter("@MaxRows", System.Data.SqlDbType.BigInt);
                    param4.Value = 10;
                    sqlcommand.Parameters.Add(param4);
                    SqlDataReader dr;
                    using (dr = sqlcommand.ExecuteReader())
                    {
                        SlowestTimerJob slowestTJ;
                        while (dr.Read())
                        {
                            slowestTJ = new SlowestTimerJob();
                            slowestTJ.MaxDuration = dr.GetInt64(3);
                            slowestTJ.AvgDuration = dr.GetInt64(2);
                            slowestTJ.MinDuration = dr.GetInt64(4);
                            slowestTJ.AvgQueryCount = dr.GetInt32(5);
                            slowestTJ.MaxQueryCount = dr.GetInt32(6);
                            slowestTJ.MinQueryCount = dr.GetInt32(7);
                            slowestTJ.TotalPageHits = dr.GetInt32(8);
                            slowestTJ.Name = dr.GetString(0);
                            slowestTJ.Server = dr.GetString(1);
                            dc.SlowestTimerJobs.InsertOnSubmit(slowestTJ);


                        }
                    }
                    SqlCommand comTopUsers;
                    foreach (WebApplication webApp in dc.WebApplications)
                    {
                        comTopUsers = new SqlCommand("proc_GetMostActiveUsers", conn);
                        comTopUsers.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlParameter paramST = new SqlParameter("@StartTime", System.Data.SqlDbType.DateTime);
                        paramST.Value = DateTime.Today.AddMonths(-1);
                        comTopUsers.Parameters.Add(paramST);
                        SqlParameter paramET = new SqlParameter("@EndTime", System.Data.SqlDbType.DateTime);
                        paramET.Value = DateTime.Today;
                        comTopUsers.Parameters.Add(paramET);
                        SqlParameter paramWebAppId = new SqlParameter("@WebApplicationId", System.Data.SqlDbType.UniqueIdentifier);
                        paramWebAppId.Value = webApp.WebApplicationID;
                        comTopUsers.Parameters.Add(paramWebAppId);
                        SqlParameter paramServ = new SqlParameter("@MachineName", System.Data.SqlDbType.NChar);
                        paramServ.Value = DBNull.Value;
                        comTopUsers.Parameters.Add(paramServ);
                        SqlParameter paramMR = new SqlParameter("@MaxRows", System.Data.SqlDbType.BigInt);
                        paramMR.Value = 10;
                        comTopUsers.Parameters.Add(paramMR);
                        SqlDataReader UsersReader;
                        using (UsersReader = comTopUsers.ExecuteReader())
                        {
                            TopActiveUser activeUser;
                            while (UsersReader.Read())
                            {
                                activeUser = new TopActiveUser();
                                activeUser.WebApplication = webApp;
                                activeUser.Login = UsersReader.GetString(0);
                                activeUser.Hits = UsersReader.GetInt32(1);
                                activeUser.LastAccessTime = UsersReader.GetDateTime(2);
                                activeUser.SuccessHitsRate = double.Parse(UsersReader[3].ToString());
                                dc.TopActiveUsers.InsertOnSubmit(activeUser);


                            }
                        }
                    }
                    SqlCommand comSlowestPages;
                    foreach (WebApplication webApp in dc.WebApplications)
                    {
                        comSlowestPages = new SqlCommand("proc_GetSlowestPages", conn);
                        comSlowestPages.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlParameter paramSPST = new SqlParameter("@StartTime", System.Data.SqlDbType.DateTime);
                        paramSPST.Value = DateTime.Today.AddMonths(-1);
                        comSlowestPages.Parameters.Add(paramSPST);
                        SqlParameter paramSPET = new SqlParameter("@EndTime", System.Data.SqlDbType.DateTime);
                        paramSPET.Value = DateTime.Today;
                        comSlowestPages.Parameters.Add(paramSPET);
                        SqlParameter paramSPWebAppId = new SqlParameter("@WebApplicationId", System.Data.SqlDbType.UniqueIdentifier);
                        paramSPWebAppId.Value = webApp.WebApplicationID;
                        comSlowestPages.Parameters.Add(paramSPWebAppId);
                        SqlParameter paramSPServ = new SqlParameter("@MachineName", System.Data.SqlDbType.NChar);
                        paramSPServ.Value = DBNull.Value;
                        comSlowestPages.Parameters.Add(paramSPServ);
                        SqlParameter paramSPMR = new SqlParameter("@MaxRows", System.Data.SqlDbType.BigInt);
                        paramSPMR.Value = 10;
                        comSlowestPages.Parameters.Add(paramSPMR);
                        SqlDataReader PagesReader;
                        using (PagesReader = comSlowestPages.ExecuteReader())
                        {
                            SlowestPage slowestPage;
                            while (PagesReader.Read())
                            {
                                slowestPage = new SlowestPage();
                                slowestPage.WebApplication = webApp;
                                slowestPage.URL = PagesReader.GetString(0);
                                slowestPage.AverageDuration = double.Parse(PagesReader[1].ToString());
                                slowestPage.MaximumDuraion = double.Parse(PagesReader[2].ToString());
                                slowestPage.MinimumDuration = double.Parse(PagesReader[3].ToString());
                                slowestPage.AverageDatabaseQueries =  int.Parse(PagesReader[4].ToString());
                                slowestPage.MaximumDatabseQueries = int.Parse(PagesReader[5].ToString());
                                slowestPage.MinimumDatabaseQueries = int.Parse(PagesReader[6].ToString());
                                slowestPage.NumberRequests = int.Parse(PagesReader[7].ToString());
                                dc.SlowestPages.InsertOnSubmit(slowestPage);


                            }
                        }
                    }

                    dc.SubmitChanges();




                }
            }
            catch (Exception ex1)
            {
                Logger.Error(ex1);
            }

            return errors;
        }

    }
}